/*
 * @(#)InitialServlet.java   
 *
 * Project: CUPSecureBM
 * 
 * Modify Information:
 *  Author          Date                Description
 * ==============   =================   =====================
 *  yonggangguo     2005-12-29             Create this file
 *
 * Copyright notice:
 *   Copyright (c) 2001-2005 Beijing HiTRUST Technology Co., Ltd.
 *   1808 Room, Science & Technology Building, No. 9 South Street,
 *   Zhong Guan Cun, Haidian District, Beijing ,100081, China
 *   All rights reserved.
 *
 *   This software is the confidential and proprietary information of
 *   HiTRUST.COM, Inc. ('Confidential Information').  You shall not
 *   disclose such Confidential Information and shall use it only in
 *   accordance with the terms of the license agreement you entered
 *   into with HiTRUST.
 */

package com.hitrust.gs.action.report;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletResponse;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.hssf.util.HSSFColor;

import javax.servlet.http.HttpServletRequest;

import com.hitrust.gs.bean.GSOperatorBean;
import com.hitrust.gs.dao.GSOrganizationDAO;
import com.hitrust.gs.lib.GSBaseConstants;
import com.hitrust.gs.lib.GSSystemConfigurator;
import com.hitrust.gs.service.GSBaseManager;
import com.hitrust.gs.util.lang.StringUtils;


public class GSStockAnalyseMonthReportServlet extends HttpServlet {
	
	public static String FILENAME="caigoufenxiyuebao.xls";
	
    public void init(ServletConfig aConfig) throws ServletException {
        super.init(aConfig);
    }

    /**
     * 查詢結算數據，最終形成一個位置控制矢量和一個數据MAP
     * @param start
     * @param end
     * @return
     */
    public Object[] query_old(String start,String end){
        Object[] obj = new Object[2]; 
        //普通
        String sql1 = "select a.gs_workcorp_id P1,c.gs_org_name P2,c.gs_upperorg_id P3, " +
        		"d.gs_org_name P4,b.gs_order_count P5,b.gs_goods_unitprice P6 " +
        		"from gs_order a,gs_order_detail b ,gs_organization c,gs_organization d where " +
        		"b.gs_signin_date >= ? and "+
        		"b.gs_signin_date <= ? and "+
        		"substr(b.gs_order_tempid,9,1) <> 'L' and " +
        		"a.gs_order_tempid = b.gs_order_tempid and " +
        		"a.gs_workcorp_id = c.gs_org_id and " +
        		"c.gs_upperorg_id = d.gs_org_id and " + 
        		"(b.gs_order_detail_status ='12' or b.gs_order_detail_status ='14') " +
        		"order by P3,P1 ";
        //應急
        String sql2 = "select a.gs_workcorp_id P1,c.gs_org_name P2,c.gs_upperorg_id P3, " +
        		"d.gs_org_name P4,b.gs_order_count P5,b.gs_goods_unitprice P6 " +
				"from gs_order a,gs_order_detail b ,gs_organization c,gs_organization d where " +
        		"b.gs_signin_date >= ? and "+
        		"b.gs_signin_date <= ? and "+
				"substr(b.gs_order_tempid,9,1) = 'L' and " +
				"a.gs_order_tempid = b.gs_order_tempid and " +
				"a.gs_workcorp_id = c.gs_org_id and " +
				"c.gs_upperorg_id = d.gs_org_id and " + 
				"(b.gs_order_detail_status ='12' or b.gs_order_detail_status ='14') " +
        		"order by P3,P1 ";

        GSOrganizationDAO dao = new GSOrganizationDAO();
        Connection conn = null;
        ResultSet rs = null;
        try{
            ArrayList ary = dao.getAllOrgForReport("");
            conn = dao.getConnetion();
            PreparedStatement pstmt1 = conn.prepareStatement(sql1);
            pstmt1.setString(1,start);
            pstmt1.setString(2,end);
            rs = pstmt1.executeQuery();
            HashMap map = new HashMap();
            obj[0] = ary;obj[1] = map;
            //先计算普通采购的数量
            if(rs!=null){
                while(rs.next()){
                    String workcorp = rs.getString(1);
                    String orgName = rs.getString(2);
                    String upporgId = rs.getString(3);
                    String upporgName = rs.getString(4);
                    String ptcg_totalPrice = new BigDecimal(rs.getString(5)).multiply(new BigDecimal(rs.getString(6))).toString();

                    //數據同步
                    if(!map.containsKey(workcorp)){    
                        String tmp[] = new String[]{workcorp,orgName,upporgId,upporgName,ptcg_totalPrice,"0"};
                        map.put(workcorp,tmp);
                    }else{
                        String[] tmp = (String[])map.get(workcorp);
                        //累加
                        tmp[4] = (new BigDecimal(tmp[4])).add(new BigDecimal(ptcg_totalPrice)).toString();    
                    }
                }
                rs.close();                
            }
            //然后计算应急采购的数量，并把应急采购的数量合成到普通采购的数量中
            PreparedStatement pstmt2 = conn.prepareStatement(sql2);
            pstmt2.setString(1,start);
            pstmt2.setString(2,end);
            rs = pstmt2.executeQuery();
            if(rs!=null){
                while(rs.next()){
                    String workcorp = rs.getString(1);
                    String orgName = rs.getString(2);
                    String upporgId = rs.getString(3);
                    String upporgName = rs.getString(4);
                    String yjcg_totalPrice = new BigDecimal(rs.getString(5)).multiply(new BigDecimal(rs.getString(6))).toString();

                    if(!map.containsKey(workcorp)){
                        String tmp[] = new String[]{workcorp,orgName,upporgId,upporgName,"0",yjcg_totalPrice};
                        map.put(workcorp,tmp);
                    }else{
                        String[] tmp = (String[])map.get(workcorp);
                        tmp[5] = (new BigDecimal(tmp[5])).add(new BigDecimal(yjcg_totalPrice)).toString();
                    }
                }
                rs.close();                
            }
        }catch(Exception e){
            
        }finally{
            GSOrganizationDAO.close(conn);
        }
        
        
        return obj;
    }

    /**
     * 查詢結算數據，最終形成一個位置控制矢量和一個數据MAP
     * @param start
     * @param end
     * @return
     */
    public Object[] query(String start,String end,GSOperatorBean bean){
        Object[] obj = new Object[2]; 
        String limitCond = "";
        if("0000".equals(bean.getGs_oper_org_id())){
            limitCond  = "1=1";
        }else{
            limitCond  = "c.gs_upperorg_id = ? or c.gs_org_id = ?";
        }
        //所有
        String sql1 = "select a.gs_workcorp_id P1,c.gs_org_name P2,c.gs_upperorg_id P3, " +
        		"d.gs_org_name P4,b.gs_order_count P5,b.gs_goods_unitprice P6 " +
        		"from gs_order a,gs_order_detail b ,gs_organization c,gs_organization d where " +
        		"b.gs_signin_date >= ? and "+
        		"b.gs_signin_date <= ? and "+
        		"a.gs_order_tempid = b.gs_order_tempid and " +
        		"a.gs_workcorp_id = c.gs_org_id and c.gs_upperorg_id = d.gs_org_id and " +
        		"("+limitCond+") and " + 
        		"(b.gs_order_detail_status ='12' or b.gs_order_detail_status ='14') " +
        		"order by P3,P1 ";
        //應急
        String sql2 = "select a.gs_workcorp_id P1,c.gs_org_name P2,c.gs_upperorg_id P3, " +
        		"d.gs_org_name P4,b.gs_order_count P5,b.gs_goods_unitprice P6 " +
				"from gs_order a,gs_order_detail b ,gs_organization c,gs_organization d where " +
        		"b.gs_signin_date >= ? and "+
        		"b.gs_signin_date <= ? and "+
				"substr(b.gs_order_tempid,9,1) = 'L' and " +
				"a.gs_order_tempid = b.gs_order_tempid and " +
				"a.gs_workcorp_id = c.gs_org_id and c.gs_upperorg_id = d.gs_org_id and " +
				"("+limitCond+") and " + 
				"(b.gs_order_detail_status ='12' or b.gs_order_detail_status ='14') " +
        		"order by P3,P1 ";
        //网上
        String sql3 = "select a.gs_workcorp_id P1,c.gs_org_name P2,c.gs_upperorg_id P3, " +
				"d.gs_org_name P4,b.gs_order_count P5,b.gs_goods_unitprice P6 " +
				"from gs_order a,(" +
				"select * from gs_order_detail where " +
				"gs_order_detail.gs_signin_date >= ? and "+
				"gs_order_detail.gs_signin_date <= ? "+
				") b ,gs_organization c,gs_organization d, " +
				"gs_manufacture e,gs_provider f where " +
				"a.gs_order_tempid = b.gs_order_tempid and " +
				"a.gs_workcorp_id = c.gs_org_id and c.gs_upperorg_id = d.gs_org_id and " +
				"("+limitCond+") and " +
				"substr(b.gs_order_tempid,9,1) <> 'L' and " +
				"b.gs_manufacture_id = e.gs_manufacture_id and " + 
				"e.gs_provider_id = f.gs_provider_id and " +
				"f.GS_IS_TRADEFLAT_PROVIDER = 0 and e.GS_IS_TRADEFLAT_MANUFACTURE <= 1 and " + 
				"(b.gs_order_detail_status ='12' or b.gs_order_detail_status ='14') " +
				"order by P3,P1 ";
        GSOrganizationDAO dao = new GSOrganizationDAO();
        Connection conn = null;
        ResultSet rs = null;
        try{
            ArrayList ary = dao.getAllOrgForReport(bean.getGs_oper_org_id());
            conn = dao.getConnetion();
            PreparedStatement pstmt1 = conn.prepareStatement(sql1);
            pstmt1.setString(1,start);
            pstmt1.setString(2,end);
            if(!"0000".equals(bean.getGs_oper_org_id())){
                pstmt1.setString(3,bean.getGs_oper_org_id());
                pstmt1.setString(4,bean.getGs_oper_org_id());
            }
            rs = pstmt1.executeQuery();
            HashMap map = new HashMap();
            obj[0] = ary;obj[1] = map;
            //先计算全部采购的数量
            if(rs!=null){
                while(rs.next()){
                    String workcorp = rs.getString(1);
                    String orgName = rs.getString(2);
                    String upporgId = rs.getString(3);
                    String upporgName = rs.getString(4);
                    String qbcg_totalPrice = new BigDecimal(rs.getString(5)).multiply(new BigDecimal(rs.getString(6))).toString();
                    
                    //數據同步
                    if(!map.containsKey(workcorp)){    
                        String tmp[] = new String[]{workcorp,orgName,upporgId,upporgName,qbcg_totalPrice,"0","0"};
                        map.put(workcorp,tmp);
                    }else{
                        String[] tmp = (String[])map.get(workcorp);
                        //累加
                        tmp[4] = (new BigDecimal(tmp[4])).add(new BigDecimal(qbcg_totalPrice)).toString();    
                    }
                }
                rs.close();                
            }
            pstmt1.close();
            //然后计算应急采购的数量，并把应急采购的数量合成到普通采购的数量中
            PreparedStatement pstmt2 = conn.prepareStatement(sql2);
            pstmt2.setString(1,start);
            pstmt2.setString(2,end);
            if(!"0000".equals(bean.getGs_oper_org_id())){
                pstmt2.setString(3,bean.getGs_oper_org_id());
                pstmt2.setString(4,bean.getGs_oper_org_id());
            }
            rs = pstmt2.executeQuery();
            if(rs!=null){
                while(rs.next()){
                    String workcorp = rs.getString(1);
                    String orgName = rs.getString(2);
                    String upporgId = rs.getString(3);
                    String upporgName = rs.getString(4);
                    String yjcg_totalPrice = new BigDecimal(rs.getString(5)).multiply(new BigDecimal(rs.getString(6))).toString();

                    
                    if(!map.containsKey(workcorp)){
                        String tmp[] = new String[]{workcorp,orgName,upporgId,upporgName,"0",yjcg_totalPrice,"0"};
                        map.put(workcorp,tmp);
                    }else{
                        String[] tmp = (String[])map.get(workcorp);
                        tmp[5] = (new BigDecimal(tmp[5])).add(new BigDecimal(yjcg_totalPrice)).toString();
                    }
                }
                rs.close();                
            }
            pstmt2.close();
            //然后计算网上采购的数量，并把网上采购的数量合成到普通采购的数量中
            PreparedStatement pstmt3 = conn.prepareStatement(sql3);
            pstmt3.setString(1,start);
            pstmt3.setString(2,end);
            if(!"0000".equals(bean.getGs_oper_org_id())){
                pstmt3.setString(3,bean.getGs_oper_org_id());
                pstmt3.setString(4,bean.getGs_oper_org_id());
            }
            rs = pstmt3.executeQuery();
            if(rs!=null){
                while(rs.next()){
                    String workcorp = rs.getString(1);
                    String orgName = rs.getString(2);
                    String upporgId = rs.getString(3);
                    String upporgName = rs.getString(4);
                    String wscg_totalPrice = new BigDecimal(rs.getString(5)).multiply(new BigDecimal(rs.getString(6))).toString();


                    if(!map.containsKey(workcorp)){
                        String tmp[] = new String[]{workcorp,orgName,upporgId,upporgName,"0","0",wscg_totalPrice};
                        map.put(workcorp,tmp);
                    }else{
                        String[] tmp = (String[])map.get(workcorp);
                        tmp[6] = (new BigDecimal(tmp[6])).add(new BigDecimal(wscg_totalPrice)).toString();
                    }
                }
                rs.close();                
            }
            pstmt3.close();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            GSOrganizationDAO.close(conn);
        }
        
        
        return obj;
    }
    public void doPost(HttpServletRequest request,HttpServletResponse response)throws ServletException {
        
        System.out.println("\ninto GSStockAnalyseMonthReportServlet");
        
    	
	    String start = request.getParameter("begindate");
        String end = request.getParameter("enddate");
        String querytype = request.getParameter("querytype");
        if("1".equals(querytype)){//如果采用的是指定月份查询
            String[] template = GSBaseManager.getPreSetDate(Integer.parseInt(start),Integer.parseInt(end));
            start = template[0].trim() + " 00:00:00";
            end = template[1].trim() + " 23:59:59";
        }else{//如果采用的是指定首未时间查询
            start = start + " 00:00:00";
            end = end + " 23:59:59";
        }

        System.out.println("\ninto GSStockAnalyseMonthReportServlet");
        System.out.println("start"+start);
        System.out.println("end"+end);
        
        GSOperatorBean operatorBean = (GSOperatorBean)request.getSession().getAttribute(GSBaseConstants.LOGIN);
        Object[] a = query(start,end,operatorBean);

        try{
            ArrayList aList = (ArrayList)a[0];
            if(aList.size() <= 0) throw new Exception();
			writeExcel(aList,(HashMap)a[1],start,end);
			
        }catch(Exception e){
	    	request.getSession().setAttribute("MSG","没有查到数据或查询异常,请重新输入查询条件");
    	    
    	    try{
    	        request.getRequestDispatcher("/A300101.do").forward(request,response);
    	    }catch(Exception ex){
    	        ex.printStackTrace();
    	    }
        }
        System.out.println("writeExcel succeed...");

        response.reset();
    	response.setContentType( "application/vnd.ms-excel;charset=UTF-16");
    	response.setHeader("Content-disposition","attachment; filename=caigoufenxiyuebao.xls" );

        try{
			String tOutputFile=GSSystemConfigurator.getParam("gs.backuppath")+"/"+FILENAME;
			String tOutputPath=GSSystemConfigurator.getParam("gs.backuppath");
	
			File tFile = new File(tOutputFile);
			FileInputStream tInputStream = new FileInputStream(tFile);
	        OutputStream tOutputStream = response.getOutputStream();
	        byte[] buff = new byte[1024];
	        int readCount = 0;
	        readCount = tInputStream.read(buff);
	        while (readCount != -1){
	            tOutputStream.write(buff, 0, readCount); 
	           readCount = tInputStream.read(buff);
	        }
	        tOutputStream.flush();
	        if (tInputStream!=null){
	            tInputStream.close();
	        }
	        if (tOutputStream!=null){
	            tOutputStream.close();
	        }
	    }catch(Exception ex){
	    	ex.printStackTrace();
    	    
	    }     
    }
    public void doGet(HttpServletRequest request,HttpServletResponse response)throws ServletException {
    	doPost(request,response);
    }
	private void writeExcel(ArrayList aList,HashMap map,String start,String end) throws Exception  {
		try{	
			HSSFWorkbook tWb = new HSSFWorkbook();
	    	HSSFSheet tSheet = tWb.createSheet();
	    	HSSFRow tRow = null;
	    	Region region = null;
	    	tSheet.setColumnWidth((short)0,(short)(20*256));
	    	tSheet.setColumnWidth((short)1,(short)(20*256));
	    	tSheet.setColumnWidth((short)2,(short)(20*256));
	    	tSheet.setColumnWidth((short)3,(short)(20*256));
	    	tSheet.setColumnWidth((short)4,(short)(20*256));
	    	tSheet.setColumnWidth((short)5,(short)(20*256));
	    	tWb.setSheetName(0,"采购分析月报表",HSSFCell.ENCODING_UTF_16);

	    	HSSFFont font = tWb.createFont();
	        font.setFontHeightInPoints((short)9);
	        font.setFontName("宋体");
	        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	        
	        HSSFFont font_title = tWb.createFont();
	        font_title.setFontHeightInPoints((short)12);
	        font_title.setFontName("宋体");
	        font_title.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

	    	HSSFCellStyle cStyle = tWb.createCellStyle();
	        cStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	        HSSFCellStyle cStyleT = tWb.createCellStyle();
	        cStyleT.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	        cStyleT.setFont(font_title);
	        
	        HSSFCellStyle cStyleM = tWb.createCellStyle();
	        cStyleM.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	        cStyleM.setFont(font);
	        cStyleM.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	        cStyleM.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	        cStyleM.setBorderRight(HSSFCellStyle.BORDER_THIN);
	        cStyleM.setBorderTop(HSSFCellStyle.BORDER_THIN);
	        cStyleM.setBottomBorderColor(HSSFColor.BLACK.index);
	        cStyleM.setTopBorderColor(HSSFColor.BLACK.index);
	        cStyleM.setRightBorderColor(HSSFColor.BLACK.index);
	        cStyleM.setLeftBorderColor(HSSFColor.BLACK.index);
	        
	        HSSFCellStyle cStyleM_R = tWb.createCellStyle();
	        cStyleM_R.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
	        cStyleM_R.setFont(font);
	        cStyleM_R.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	        cStyleM_R.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	        cStyleM_R.setBorderRight(HSSFCellStyle.BORDER_THIN);
	        cStyleM_R.setBorderTop(HSSFCellStyle.BORDER_THIN);
	        cStyleM_R.setBottomBorderColor(HSSFColor.BLACK.index);
	        cStyleM_R.setTopBorderColor(HSSFColor.BLACK.index);
	        cStyleM_R.setRightBorderColor(HSSFColor.BLACK.index);
	        cStyleM_R.setLeftBorderColor(HSSFColor.BLACK.index);
	        
	        HSSFCellStyle cStyleM_F = tWb.createCellStyle();
	        cStyleM_F.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	        cStyleM_F.setFont(font);
	        
	    	//标题行
	    	tRow = tSheet.createRow((short)0);
	    	HSSFCell c0 = createCell(tWb,tRow,(short)2,"采购分析月报表");
	    	c0.setCellStyle(cStyleT);
	    	
	    	//日期行
	    	tRow = tSheet.createRow((short)1);
	    	region = new Region(1, (short)4, 1, (short)5);
	    	tSheet.addMergedRegion(region);
	    	createCell(tWb,tRow,(short)4,start+" 至 "+end,HSSFCellStyle.ALIGN_CENTER,false);
	    	
	    	//表头行
	        tRow = tSheet.createRow((short)2);
	    	region = new Region(2, (short)0, 2, (short)1);
	    	tSheet.addMergedRegion(region);
	    	HSSFCell c1 = createCell(tWb,tRow,(short)0,"订货单位");
	    	HSSFCell c1_ = createCell(tWb,tRow,(short)1,"");
	    	HSSFCell c2 = createCell(tWb,tRow,(short)2,"集中采购额（元）");
	    	HSSFCell c3 = createCell(tWb,tRow,(short)3,"网上采购额（元）");
	    	HSSFCell c4 = createCell(tWb,tRow,(short)4,"应急采购额（元）");
	    	HSSFCell c5 = createCell(tWb,tRow,(short)5,"小计");
	    	c1.setCellStyle(cStyleM);c1_.setCellStyle(cStyleM);
	    	c2.setCellStyle(cStyleM);c3.setCellStyle(cStyleM);
	    	c4.setCellStyle(cStyleM);c5.setCellStyle(cStyleM);
	    	
	    	String[] tTemp;
	    	BigDecimal lastValue = new BigDecimal("0");
 
	    	int regionBegin = 0;
	    	int regionEnd = 0;
	    	String currentyy = "";
	    	int index = 2;//当前所在行号	    	
	    	int index_b = 3;
	    	BigDecimal zongji = new BigDecimal("0");
	    	
	    	for(int i=0;i<aList.size();i++){
	    	    String[] orgList = (String[])aList.get(i);
	    	    //當前運營公司或者修理廠有狀態為已簽收的訂貨申請的，需要寫入excel文件
	    	    if(map.containsKey(orgList[0])){
	    	        //創建一行
		    		tRow = tSheet.createRow((short)(index+1));
		    		//得到該單位的採購額之和，為一個數組
		    		tTemp=(String [])map.get(orgList[0]);
		    		//如果發現本單位所屬的運營公司和上一條記錄不同，説明上一條記錄所屬的運營公司已經統計完畢
		    		//要為他增加一個統計行
		    		
		    		if(!currentyy.equals(tTemp[2])){
		    		    //如果本行為第一行，則不需要增加統計行
		    		    //如果本行不是第一行，則需要增加統計行
		    		    if(!"".equals(currentyy)){
		    		        region = new Region(index+1,(short)1,index+1,(short)5);
		    		        tSheet.addMergedRegion(region);
		    		        HSSFCell tongjiCell = tRow.createCell((short)1);
		    		        HSSFCell tongjiCell2 = tRow.createCell((short)2);
		    		        HSSFCell tongjiCell3 = tRow.createCell((short)3);
		    		        HSSFCell tongjiCell4 = tRow.createCell((short)4);
		    		        HSSFCell tongjiCell5 = tRow.createCell((short)5);
		    		        
		    		        tongjiCell.setEncoding(HSSFCell.ENCODING_UTF_16);
		    		        String viewValue = StringUtils.format(lastValue.toString(),2);
		    		        tongjiCell.setCellValue("合计:"+viewValue+"元");
		    		        tongjiCell.setCellStyle(cStyleM_R);
		    		        tongjiCell2.setCellStyle(cStyleM_R);
		    		        tongjiCell3.setCellStyle(cStyleM_R);
		    		        tongjiCell4.setCellStyle(cStyleM_R);
		    		        tongjiCell5.setCellStyle(cStyleM_R);

		    		        //把公司名称合并单元格
		    		        region = new Region(index_b,(short)0,index+1,(short)0);
		    		        tSheet.addMergedRegion(region);
		    		        index_b = index+2;
		    
		    		        zongji = zongji.add(new BigDecimal(lastValue.toString()));
		    		        lastValue = new BigDecimal("0");
		    		        index++;
		    		        //創建的row被增加的小计行佔用了，新增加一行
		    		        tRow = tSheet.createRow((short)(index+1));
		    		    }
		    		    currentyy = "0000".equals(tTemp[2])?tTemp[0]:tTemp[2];
		    		}

	    		    createCell(tWb,tRow,(short)0,"0000".equals(tTemp[2])?tTemp[1]:tTemp[3],HSSFCellStyle.ALIGN_RIGHT,HSSFCellStyle.VERTICAL_CENTER,true);
		    		createCell(tWb,tRow,(short)1,tTemp[1],HSSFCellStyle.ALIGN_RIGHT,true);
		    		createCell(tWb,tRow,(short)2,StringUtils.fmtDbl(tTemp[4],2),HSSFCellStyle.ALIGN_RIGHT,true);
		    		createCell(tWb,tRow,(short)3,StringUtils.fmtDbl(tTemp[6],2),HSSFCellStyle.ALIGN_RIGHT,true);
		    		createCell(tWb,tRow,(short)4,StringUtils.fmtDbl(tTemp[5],2),HSSFCellStyle.ALIGN_RIGHT,true);
		    		BigDecimal temp = new BigDecimal(tTemp[4]);//.add(new BigDecimal(tTemp[5])).add(new BigDecimal(tTemp[6]));
		    		createCell(tWb,tRow,(short)5,StringUtils.fmtDbl(tTemp[4],2) + "元",HSSFCellStyle.ALIGN_RIGHT,true);
		    		lastValue = lastValue.add(temp);
	    	        index++;
	    	    }
	    	}
	    	
	    	if(index > 2){
		    	//創建最後一個統計行
		    	tRow = tSheet.createRow((short)(index+1));
		        
		        HSSFCell tongjiCell = tRow.createCell((short)1);
		        HSSFCell tongjiCell0 = tRow.createCell((short)0);
		        HSSFCell tongjiCell2 = tRow.createCell((short)2);
		        HSSFCell tongjiCell3 = tRow.createCell((short)3);
		        HSSFCell tongjiCell4 = tRow.createCell((short)4);
		        HSSFCell tongjiCell5 = tRow.createCell((short)5);
		        tongjiCell.setEncoding(HSSFCell.ENCODING_UTF_16);
		        String viewValue = StringUtils.fmtDbl(lastValue.toString(),2);
		        tongjiCell.setCellValue("合计:"+viewValue+"元");
		        tongjiCell.setCellStyle(cStyleM_R);
		        tongjiCell0.setCellStyle(cStyleM_R);
		        tongjiCell2.setCellStyle(cStyleM_R);
		        tongjiCell3.setCellStyle(cStyleM_R);
		        tongjiCell4.setCellStyle(cStyleM_R);
		        tongjiCell5.setCellStyle(cStyleM_R);

		        region = new Region(index+1,(short)1,index+1,(short)5);
		        tSheet.addMergedRegion(region);
		        region = new Region(index_b,(short)0,index+1,(short)0);
		        tSheet.addMergedRegion(region);
		        zongji = zongji.add(new BigDecimal(lastValue.toString()));
	    	}
	    	if(index > 2){
		    	tRow = tSheet.createRow((short)(index+2));
		        
		        HSSFCell tongjiCell = tRow.createCell((short)1);
		        HSSFCell tongjiCell0 = tRow.createCell((short)0);
		        HSSFCell tongjiCell2 = tRow.createCell((short)2);
		        HSSFCell tongjiCell3 = tRow.createCell((short)3);
		        HSSFCell tongjiCell4 = tRow.createCell((short)4);
		        HSSFCell tongjiCell5 = tRow.createCell((short)5);
		        tongjiCell0.setEncoding(HSSFCell.ENCODING_UTF_16);
		        String viewValue = StringUtils.fmtDbl(zongji.toString(),2);
		        tongjiCell0.setCellValue("总计:"+viewValue+"元");
		        tongjiCell.setCellStyle(cStyleM_R);
		        tongjiCell0.setCellStyle(cStyleM_R);
		        tongjiCell2.setCellStyle(cStyleM_R);
		        tongjiCell3.setCellStyle(cStyleM_R);
		        tongjiCell4.setCellStyle(cStyleM_R);
		        tongjiCell5.setCellStyle(cStyleM_R);

		        region = new Region(index+2,(short)0,index+2,(short)5);
		        tSheet.addMergedRegion(region);
	    	}
	    	
	    	File theFile = new File(GSSystemConfigurator.getParam("gs.backuppath")+"/"+FILENAME);
	    	if(theFile.exists()) theFile.delete();
	    	theFile.createNewFile();
	    	FileOutputStream fileOut = new FileOutputStream(theFile);
	    	
	    	tWb.write(fileOut);
	    	fileOut.flush();
	    	fileOut.close();
		}catch(Exception ex){
			ex.printStackTrace();
			throw ex;
		}
	}
	private HSSFCell createCell(HSSFWorkbook aWb, HSSFRow aRow, short column,String aValue){
	    HSSFCell tCell = aRow.createCell(column);
	    tCell.setEncoding(HSSFCell.ENCODING_UTF_16);
	    tCell.setCellValue(aValue);
	    return tCell;
	}
	
	private HSSFCell createCell(HSSFWorkbook aWb, HSSFRow aRow, short column,String aValue, short aAlign, boolean border){
	    HSSFCell tCell = aRow.createCell(column);
	    tCell.setEncoding(HSSFCell.ENCODING_UTF_16);
	    tCell.setCellValue(aValue);
	    HSSFCellStyle tCellStyle = aWb.createCellStyle();
	    tCellStyle.setAlignment(aAlign);
	    if(border){
	        tCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	        tCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	        tCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	        tCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	        tCellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
	        tCellStyle.setTopBorderColor(HSSFColor.BLACK.index);
	        tCellStyle.setRightBorderColor(HSSFColor.BLACK.index);
	        tCellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
	    }
	    tCell.setCellStyle(tCellStyle);
	    return tCell;
	}
	
	private HSSFCell createCell(HSSFWorkbook aWb, HSSFRow aRow, short column,String aValue, short aAlign, short bAlign, boolean border){
	    HSSFCell tCell = aRow.createCell(column);
	    tCell.setEncoding(HSSFCell.ENCODING_UTF_16);
	    tCell.setCellValue(aValue);
	    HSSFCellStyle tCellStyle = aWb.createCellStyle();
	    tCellStyle.setAlignment(aAlign);
	    tCellStyle.setVerticalAlignment(bAlign);
	    if(border){
	        tCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	        tCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	        tCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
	        tCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
	        tCellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
	        tCellStyle.setTopBorderColor(HSSFColor.BLACK.index);
	        tCellStyle.setRightBorderColor(HSSFColor.BLACK.index);
	        tCellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
	    }
	    tCell.setCellStyle(tCellStyle);

	    return tCell;
	}
	
	public static void main(String[] args){
		try{	
			HSSFWorkbook tWb = new HSSFWorkbook();
	    	HSSFSheet tSheet = tWb.createSheet();
	    	tWb.setSheetName(0,"采购分析月报表",HSSFCell.ENCODING_UTF_16);
	    	HSSFRow tRow = tSheet.createRow((short)0);
	    	//起始行,起始列,结束行,结束列,起始和结束相同,表示只有1列
	    	Region region = new Region(5, (short)0, 4, (short)1);

	    	tSheet.addMergedRegion(region);
	    	HSSFRow row = tSheet.createRow((short)8);
	    	HSSFCell cell = row.createCell((short)3);
	    	cell.setCellValue("aaaa");
	    	
	    	File tFile = new File("c:/a.xls");
	    	FileOutputStream fileOut = new FileOutputStream(tFile);
	    	tWb.write(fileOut);
	    	fileOut.flush();
	    	fileOut.close();
		}catch(Exception e){
		    e.printStackTrace();
		}
	}
}
